package dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import entity.Notice;
import util.DbUtil;

public class NoticeDao {

	//前台系统，查询最新添加或修改的一条公告
	public Notice getRecentNotice() throws SQLException {
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		String sql = "select * from notice order by n_time desc limit 0,1";
		return run.query(conn,sql, new BeanHandler<Notice>(Notice.class));
	}
	//后台查询所有公告
	public List<Notice> getAllNotices() throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select * from notice order by n_time desc limit 0,10";
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		return run.query(conn,sql, new BeanListHandler<Notice>(Notice.class));
	}
	//后台添加公告
	public void addNotice(Notice bean) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "insert into notice(title,details,n_time) values(?,?,?)";
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		run.update(conn,sql, bean.getTitle(),bean.getDetails(),bean.getN_time());

	}
	/*
	 * 后台修改公告
	 */
	public void updateNotice(Notice n) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "update notice set title=?,details=?,n_time=? where n_id=?";
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		run.update(conn,sql, n.getTitle(),n.getDetails(),n.getN_time(),n.getN_id());
	}
	/*
	 * 后台删除公告
	 */
	public void deleteNotice(String n_id) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "delete from notice where n_id = ?";
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		run.update(conn,sql, n_id);
	}
	/*
	 * 根据id查询公告
	 */
	public Notice findNoticeById(String n_id) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select * from notice where n_id = ?";
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		return run.query(conn,sql, new BeanHandler<Notice>(Notice.class),n_id);
	}
}
